<?php
/**
 * SpecialController is for all specials.
 * 
 * Bocheng Chen, 18.07.2012
 * 
 * LBB Solutions Oy 版权所有 @ 2012
 */
class SpecialController extends Controller
{
	/**
	 * AJAX View 
	 */
	public function actionView($id)
	{
		$params = array(
			':id' => $id,
		);
		//if loged in 
		$s_status = ', NULL as redeemed_status, NULL as redeemed_time ';
		if (!Yii::app()->user->isGuest) {
			$s_status = ', (SELECT r3.redeemed FROM tbl_merchant_special_redeem r3
					WHERE r3.id_special=s.id AND r3.id_user=:id_user AND DATE(r3.insert_timestamp)=DATE(NOW())
					ORDER BY r3.insert_timestamp DESC LIMIT 1) AS redeemed_status
					, (SELECT unix_timestamp(r4.redeemed_time) FROM tbl_merchant_special_redeem r4
					 WHERE r4.id_special=s.id AND r4.id_user=:id_user AND DATE(r4.insert_timestamp)=DATE(NOW())
					 ORDER BY r4.insert_timestamp DESC LIMIT 1) AS redeemed_time ';// add redeemed status only for today
			$params[':id_user'] = Yii::app()->user->id;
		}
		
		$sql_offer = 'SELECT s.id, s.id_merchant, s.url_picture, m.url_logo, s.content,
			s.price, s.value, s.discount, s.show_hours, s.start_hour, s.end_hour, s.week_days, s.status, s.type, s.max_number, s.redeem_type,
			day_1_start_hour, day_2_start_hour, day_3_start_hour, day_4_start_hour, day_5_start_hour, day_6_start_hour, day_7_start_hour,
			day_1_end_hour, day_2_end_hour, day_3_end_hour, day_4_end_hour, day_5_end_hour, day_6_end_hour, day_7_end_hour,
			UNIX_TIMESTAMP(s.published_time) AS uts_published_time, UNIX_TIMESTAMP(s.start_date) AS uts_start_date, UNIX_TIMESTAMP(s.end_date) AS uts_end_date,
			(SELECT SUM(r1.number) FROM tbl_merchant_special_redeem r1 WHERE r1.id_special=s.id AND DATE(r1.insert_timestamp)=DATE(NOW()) ) AS claimed_num,
			m.seo_id, m.name, m.category_key, m.url_logo, m.url_offer_img, m.phone, m.address, m.biz_area, m.postcode, m.city, m.latitude, m.longitude '
			. $s_status . '
			FROM tbl_merchant_special s
			INNER JOIN tbl_merchant_account m ON m.id=s.id_merchant
			WHERE s.trash=0 AND s.status=1 AND s.id=:id ';
		$connection = Yii::app()->db;
		$command = $connection->createCommand($sql_offer);
		$row = $command->queryRow(true, $params);
		
		//+1 viewed
		$userId = Yii::app()->user->isGuest ? null : Yii::app()->user->id;
		$ipAddress = Yii::app()->request->getUserHostAddress();
		$sql_insert = 'INSERT INTO tbl_merchant_special_viewed (id_user, id_special, ip, agent, action) VALUES (:id_user, :id_special, :ip, :agent, :action)';
		$command = $connection->createCommand($sql_insert);
		$command->execute(array(
			':id_user'=>$userId,
			':id_special'=>$id,
			':ip'=>$ipAddress,
			':agent'=>Yii::app()->request->getUserAgent(),
			':action'=>0,//0 - web view
		));
		
		//search other offers nearby
		if (!Yii::app()->request->isAjaxRequest) {
			$sql_offers_nearby = 'SELECT s.id, s.id_merchant, s.url_picture, s.content, s.show_hours, s.start_hour, s.end_hour, s.week_days, s.price, s.value, s.discount, s.type, s.max_number,
				UNIX_TIMESTAMP(s.published_time) AS uts_published_time, UNIX_TIMESTAMP(s.start_date) AS uts_start_date, UNIX_TIMESTAMP(s.end_date) AS uts_end_date,
				(6371 * acos(cos(radians(:lat)) * cos(radians(m.latitude)) * cos(radians(m.longitude) - radians(:lng)) + sin(radians(:lat)) * sin(radians(m.latitude)))) AS distance,
				m.seo_id, m.name, m.category_key, m.url_logo, m.url_offer_img, m.phone, m.address, m.biz_area, m.postcode, m.city, m.latitude, m.longitude
				FROM tbl_merchant_special s
				INNER JOIN tbl_merchant_account m ON m.id=s.id_merchant
				WHERE s.trash=0 AND s.status=1 AND s.start_date<NOW() AND s.end_date>NOW() 
				  AND s.id<>:id_special
				ORDER BY distance ASC, s.published_time DESC
				LIMIT 10';
			$command = $connection->createCommand($sql_offers_nearby);
			$special_rows = $command->queryAll(true, array(
				':id_special' => $id,
				':lat' => $row['latitude'],
				':lng' => $row['longitude'],
			));
		}
		$connection->active = false;
		
		//set discount
		$discount = '';
		if (!empty($row['discount'])) {
			$discount = sprintf('-%d', $row['discount'] * 100) . '%';
		}
		
		//set title
		if (!empty($row['price']) and !empty($row['value'])) $template = 'special_auto_title_lbl';
		elseif (!empty($row['discount'])) $template = 'special_discount_title_lbl';
		else $template = 'special_default_title_lbl';
		$this->header['title'] = BUtils::T($template, array(
			'{merchant_name}' => $row['name'],
			'{discount}' => $discount,
			'{price}' => Yii::app()->locale->numberFormatter->formatCurrency($row['price'], 'EUR'),
			'{value}' => Yii::app()->locale->numberFormatter->formatCurrency($row['value'], 'EUR'),
		));
		$this->pageTitle = $this->header['title'];
		//set offer picture
		$url_thumb_default = Yii::app()->baseUrl . '/img/default_special_thumb.png';
		$url_picture_default = Yii::app()->baseUrl . '/img/default_special_picture.png';
		$url_thumb = empty($row['url_picture']) ? $row['url_offer_img'] : $row['url_picture'];
		if (!empty($url_thumb)) {
			$url_thumb = BUtils::GetOfferPictureUrl($url_thumb);
			$url_picture = str_replace('thumb_', '', $url_thumb);
		} else {
			$url_thumb = $url_thumb_default;
			$url_picture = $url_picture_default;
		}
		$this->header['image'] = Yii::app()->params['siteRoot'] . $url_picture;
		$this->header['description'] = CHtml::encode(strip_tags(trim($row['content'])));
		
		//set meta facebook
		$og_url = Yii::app()->createAbsoluteUrl('/special/view', array('id'=>$row['id']));
		$this->meta_facebook = <<<HTML
<meta property="og:title" content="{$this->pageTitle}" />
<meta property="og:description" content="{$this->header['description']}" />
<meta property="og:type" content="website" />
<meta property="og:url" content="{$og_url}" />
<meta property="og:image" content="{$this->header['image']}" />
<meta property="og:site_name" content="Yamii.fi" />
HTML;
		
		if (Yii::app()->request->isAjaxRequest) {
			$this->renderPartial('_preview', array('row' => $row));
		} else {
			$this->render('view', array('row' => $row, 'special_rows' => $special_rows));
		}
	}
	
	/**
	 * Redeem a special.
	 * input: id, uid, lat, lng
	 */
	public function actionRedeem()
	{
		if (Yii::app()->user->isGuest) {
			BUtils::sendJSON(500, BUtils::T('no_login_redeem_offer_msg'));
		}
		
		// get user id
		$id_user = Yii::app()->user->id;
		
		// get offer id
		$id_special = Yii::app()->request->getParam('id');
		if (empty($id_special)) {
			BUtils::sendJSON(500, 'The parameter "id" is missing!');
		}
		
		$connection = Yii::app()->db;
		//insert redeems info
		$sql_redeem_ins = 'INSERT INTO tbl_merchant_special_redeem (id_user, id_special, redeemed, redeemed_time, ip, agent)
			VALUES(:id_user, :id_special, 2, NOW(), :ip, :agent)';
		$params_ins = array(
			':id_special' => $id_special,
			':id_user' => $id_user,
			':ip' => Yii::app()->request->getUserHostAddress(),
			':agent' => Yii::app()->request->getUserAgent(),
		);
		$logData = $params_ins;
		
		//fetch offer status
		$sql_status = 'SELECT s.id, s.max_number, s.week_days, s.show_hours, s.start_hour, s.end_hour, UNIX_TIMESTAMP(s.start_date) as start_date_ts, UNIX_TIMESTAMP(s.end_date) as end_date_ts, '
			. ' (SELECT SUM(r1.number) FROM tbl_merchant_special_redeem r1 WHERE r1.id_special=s.id AND DATE(r1.insert_timestamp)=DATE(NOW()) ) AS claimed_num, '	// calculate today's claimed_num
			. ' (SELECT SUM(r2.number) FROM tbl_merchant_special_redeem r2 WHERE r2.id_special=s.id AND r2.redeemed=2 AND DATE(r2.redeemed_time)=DATE(NOW()) ) AS redeemed_num '	// calculate today's redeemed_num
			. ' FROM tbl_merchant_special s
			WHERE s.id=:id_special ';
		$params = array(
			':id_special' => $id_special,
		);
		//check if user can redeem this special
		$command = $connection->createCommand($sql_status);
		$row = $command->queryRow(true, $params);
		
		$message = null;
		$error_response = null;
		$now = time();
		$week_no = date('N');
		if (empty($row)) {
			//0. check empty
			$message = "special/redeem : [web] user {$id_user} redeems an expried offer {$id_special} at " . date('H:i:s');
			BUtils::Logging('redeem_error', array($message, $logData));
			$error_response = BUtils::T('special_expired_msg');
		} elseif (!empty($row['week_days']) and false === strpos($row['week_days'], $week_no)) {
			//0. check if not today
			$message = "special/redeem : [web] user {$id_user} redeems an not today offer {$id_special} at " . date('H:i:s');
			BUtils::Logging('redeem_error', array($message, $logData));
			$error_response = BUtils::T('special_not_open_today_msg');
		} elseif ($row['show_hours'] == 1 and $row['start_hour']<$row['end_hour'] and (date('Gi')<$row['start_hour'] or $now < $row['start_date_ts'])) {
			//1. check start hour
			$message = "special/redeem : [web] user {$id_user} redeems an not start offer {$id_special} at " . date('H:i:s');
			BUtils::Logging('redeem_error', array($message, $logData));
			$error_response = BUtils::T('special_not_start_msg');
		} elseif ($row['show_hours'] == 1 and $row['start_hour']<$row['end_hour'] and (date('Gi')>$row['end_hour'] or $now > $row['end_date_ts'])) {
			//2. check end hour
			$message = "special/redeem : [web] user {$id_user} redeems an expried offer {$id_special} at " . date('H:i:s');
			BUtils::Logging('redeem_error', array($message, $logData));
			$error_response = BUtils::T('special_expired_msg');
		} elseif ($row['max_number'] > 0 and $row['max_number'] <= $row['claimed_num']) {
			//3. check if no more offer left
			$message = "special/redeem : [web] user {$id_user} redeems but no more offer left.";
			BUtils::Logging('redeem_error', array($message, $logData));
			$error_response = BUtils::T('special_given_out_msg');
		} else {
			//allow user redeem an offer
			$command = $connection->createCommand($sql_redeem_ins);
			$command->execute($params_ins);
			$message = "special/redeem : [web] user {$id_user} redeems an offer at " . date('H:i:s');
		}
		
		$sql_logging = 'INSERT INTO tbl_merchant_special_redeem_log (id_user, id_special, action, message)
			VALUES (:id_user, :id_special, :action, :message)';
		$msg_params = array(
			':id_special' => $id_special,
			':id_user' => $id_user,
			':action' => 'redeem',
			':message' => $message,
		);
		if (!empty($message)) {
			//logging message
			$command = $connection->createCommand($sql_logging);
			$command->execute($msg_params);
		}
		$connection->active = false;
		
		//stop and send error message
		if (!empty($error_response)) {
			BUtils::sendJSON(500, $error_response);
		} else {
			//async trigger:
			$url = Yii::app()->createAbsoluteUrl('/special/recommend');
			BUtils::curl_post_async($url, array(
				'id_user' => $id_user,
				'id_special' => $id_special
			));
		}
		
		BUtils::sendJSON(200, BUtils::T('special_unlocked_eg', array('{time}' => date('H:i'))));
	}
	
	/**
	 * Recommend 3 offers nearby based on user and offer
	 */
	public function actionRecommend() {
		$id_user = Yii::app()->request->getPort('id_user');
		$id_special = Yii::app()->request->getPort('id_special');
		
		if (empty($id_user) or empty($id_special)) {
			//
		}
		
		sleep(10);
		
		BUtils::Logging('curl_post_async', array($id_user, $id_special));
		
		//TODO: find offer for this user and send email
	}
	
	/**
	 * Create special code when user click get button.
	 * This function will be moved to users page.
	 */
	public function actionCode($id)
	{
		if ($id < 1) throw new CHttpException(404, 'The requested page does not exist.');
		if (Yii::app()->user->isGuest) {
			//$loginUrl = Yii::app()->createUrl('/accounts/login', array('goto'=>Yii::app()->request->getUrlReferrer()));
			$mobileAppUrl = Yii::app()->createUrl('/mobile/download', array('from'=>'web', 'action'=>'claim'));
			echo '<b>This particular deal is only available on our mobile app.</b>
				With the app, all our free deals are waiting for you! :D <br />
				<a href="'.$mobileAppUrl.'">Get free app here!</a>';
			Yii::app()->end();
		}
		
		//check table tbl_merchant_special_redeem
		//redeemed_timestamp - INTERVAL 12 HOUR > NOW(): user cannot get new special until it has been redeemed for 12 hours.
		//OR redeemed_timestamp IS NULL: or user didn't use it (wasn't redeemed by merchant)
		//CRON JOB: every month, data transfer job will move all redeemed special for one month ago. (tbl_merchant_special_redeem_hist)
		$connection = Yii::app()->db;
		$params = array('id_special'=>$id, 'id_user'=>Yii::app()->user->id);
		$sql_redeem = 'SELECT id, code, insert_timestamp, redeemed, redeemed_time
			FROM tbl_merchant_special_redeem
			WHERE id_special=:id_special AND id_user=:id_user
			  AND ((redeemed_time > NOW() - INTERVAL 12 HOUR) OR redeemed_time IS NULL)
			ORDER BY redeemed_time DESC LIMIT 1';
		$command = $connection->createCommand($sql_redeem);
		$row = $command->queryRow(true, $params);
		if (empty($row)) {
			//insert
			$sql_redeem_new = 'INSERT INTO tbl_merchant_special_redeem (id_user, id_special) VALUES (:id_user, :id_special)';
			$command = $connection->createCommand($sql_redeem_new);
			$command->execute($params);
			
			$sql_redeem = 'SELECT id FROM tbl_merchant_special_redeem
				WHERE id_special=:id_special AND id_user=:id_user AND redeemed_time IS NULL
				ORDER BY insert_timestamp DESC LIMIT 1';
			$command = $connection->createCommand($sql_redeem);
			$row = $command->queryRow(true, $params);
			$redeem_id = $row['id'];
			
			$code = BUtils::encode($redeem_id);
			//update redeem code to table
			$sql_update = 'UPDATE tbl_merchant_special_redeem SET code=:code WHERE id=:id';
			$command = $connection->createCommand($sql_update);
			$command->execute(array('code'=>$code, 'id'=>$redeem_id));
		} else {
			$redeem_id = $row['id'];
		}
		$connection->active = false;
		
		$code = BUtils::encode($redeem_id);   // redemption code = encode(redeem_id)
		$url = $code;	// redeem link: merchant use this link to redeem special.
		
		//print out HTML for redemption code
		echo '<div class="redemption_code">Yamii code: <span>' . $code . '</span></div>';
		echo '<div class="redemption_qrcode">';
		$this->widget('application.extensions.qrcode.QRCodeGenerator',array(
			'data' => $url,
			'subfolderVar' => true,
			'filename' => $code.'.png',
			'matrixPointSize' => 3,
		));
		echo '</div>';
		echo '<div class="redemption_code_tips"><b>This particular deal is only available on our mobile app.</b>
				With the app, all our free deals are waiting for you! :D <br />
				<a href="'.$mobileAppUrl.'">Get free app here!</a></div>';
		echo '<div class="clearfix"></div>';
	}
}